1. 本章节中所用到的表

# models.py

# 班级表

class Classes(models.Model):

    id = models.AutoField(primary_key=True)

    name = models.CharField(max_length=10)

# 教师表

class Teacher(models.Model):

    id = models.AutoField(primary_key=True)

    name = models.CharField(max_length=10)

    classes = models.ManyToManyField(to='Classes')

# 学生表

class Student(models.Model):

    id = models.AutoField(primary_key=True)

    name = models.CharField(max_length=10)

    age = models.IntegerField()

    classes = models.ForeignKey(to='Classes')

    student_info = models.OneToOneField(to='StudentInfo')

# 学生详情表

class StudentInfo(models.Model):

    address = models.CharField(max_length=32)

2. 错误用法

• 不要在通过.all()方法查询到的数据中进行跨表查询，因为这样会影响性能

student_list = Student.objects.all()

for student in student_list:

    print(student.name)  # Kevin -> 直接从student对象中获取

    print(student.age)  # 18 -> 直接从student对象中获取

    print(student.classes.name)  # 一班 -> 需要再执行SQL语句查询获取 -> 注意: 不要在通过.all()方法查询到的数据中进行跨表查询，因为 student.name 和 student.age 都是直接在student对象中获取数据的，而执行到 student.classes.name 这里的时候就要再执行一次 SQL 查询将当前student所关联的 classes.name 查询出来，如果 student_list 有500条数据，那么 student.classes.name 就要执行500条SQL语句

"""

    所执行的SQL语句次数

        select "app01_student"."id", "app01_student"."name", "app01_student"."age", "app01_student"."classes_id", "app01_student"."student_info_id" from "app01_student"; args=()

        select "app01_classes"."id", "app01_classes"."name" from "app01_classes" where "app01_classes"."id" = 1; args=(1,)

        select "app01_classes"."id", "app01_classes"."name" from "app01_classes" where "app01_classes"."id" = 1; args=(1,)

        select "app01_classes"."id", "app01_classes"."name" from "app01_classes" where "app01_classes"."id" = 2; args=(2,)

"""

3..select_related()

• 说明
• select_related主要针一对一和一对多（正向查询）关系进行优化
• select_related使用SQL的JOIN语句进行优化，通过减少SQL查询的次数来进行优化、提高性能
• 性能相关：表之间进行join连表操作，一次性获取关联的数据

• 不传参 -> 代表当前表 join 与当前表相关联的所有表

• 用法一

student_list = Student.objects.all().select_related()

for student in student_list:

    print(student.name)  # Kevin -> 直接从student对象中获取

    print(student.age)  # 18 -> 直接从student对象中获取

    print(student.classes.name)  # 一班 -> 直接从student对象中获取

    print(student.student_info.address)  # 横沥 -> 直接从student对象中获取

"""

    所执行的SQL语句次数

        select "app01_student"."id", "app01_student"."name", "app01_student"."age", "app01_student"."classes_id", "app01_student"."student_info_id", "app01_classes"."id", "app01_classes"."name", "app01_studentinfo"."id", "app01_studentinfo"."address" from "app01_student" inner join "app01_classes" on ("app01_student"."classes_id" = "app01_classes"."id") inner join "app01_studentinfo" on ("app01_student"."student_info_id" = "app01_studentinfo"."id"); args=()

"""

• 用法二

# 查询每个学生所在的班级

ret = Student.objects.all().select_related().values('name', 'classes__name')

ret = Student.objects.all().select_related().filter(age=18)

• 传参 -> 代表当前表 join 指定与当前表相关联的表

• 语法: .select_related('外键字段名', '外键字段名', ……)

• 用法一

student_list = Student.objects.all().select_related('classes')

for student in student_list:

    print(student.name)  # Kevin -> 直接从student对象中获取

    print(student.age)  # 18 -> 直接从student对象中获取

    print(student.classes.name)  # 一班 -> 直接从student对象中获取

"""

    所执行的SQL语句次数

        SELECT "app01_student"."id", "app01_student"."name", "app01_student"."age", "app01_student"."classes_id", "app01_student"."student_info_id", "app01_classes"."id", "app01_classes"."name" FROM "app01_student" INNER JOIN "app01_classes" ON ("app01_student"."classes_id" = "app01_classes"."id"); args=()

"""

student_list = Student.objects.all().select_related('classes')

for student in student_list:

    print(student.name)  # Kevin -> 直接从student对象中获取

    print(student.age)  # 18 -> 直接从student对象中获取

    print(student.classes.name)  # 一班 -> 直接从student对象中获取

    print(student.student_info.address)  # 横沥 -> 需要再执行SQL语句查询获取 -> 注意: 如果 select_related 没有指定 student_info 外键字段那么也会再执行SQL语句查询获取，和上面的错误用法的注意事项一样

"""

    所执行的SQL语句次数

        SELECT "app01_student"."id", "app01_student"."name", "app01_student"."age", "app01_student"."classes_id", "app01_student"."student_info_id", "app01_classes"."id", "app01_classes"."name" FROM "app01_student" INNER JOIN "app01_classes" ON ("app01_student"."classes_id" = "app01_classes"."id"); args=()

        SELECT "app01_studentinfo"."id", "app01_studentinfo"."address" FROM "app01_studentinfo" WHERE "app01_studentinfo"."id" = 1; args=(1,)

        SELECT "app01_studentinfo"."id", "app01_studentinfo"."address" FROM "app01_studentinfo" WHERE "app01_studentinfo"."id" = 2; args=(2,)

        SELECT "app01_studentinfo"."id", "app01_studentinfo"."address" FROM "app01_studentinfo" WHERE "app01_studentinfo"."id" = 3; args=(3,)

"""

student_list = Student.objects.all().select_related('classes', 'student_info')

for student in student_list:

    print(student.name)  # Kevin -> 直接从student对象中获取

    print(student.age)  # 18 -> 直接从student对象中获取

    print(student.classes.name)  # 一班 -> 直接从student对象中获取

    print(student.student_info.address)  # 横沥 -> 直接从student对象中获取

"""

    所执行的SQL语句次数

        SELECT "app01_student"."id", "app01_student"."name", "app01_student"."age", "app01_student"."classes_id", "app01_student"."student_info_id", "app01_classes"."id", "app01_classes"."name", "app01_studentinfo"."id", "app01_studentinfo"."address" FROM "app01_student" INNER JOIN "app01_classes" ON ("app01_student"."classes_id" = "app01_classes"."id") INNER JOIN "app01_studentinfo" ON ("app01_student"."student_info_id" = "app01_studentinfo"."id"); args=()

"""

• 用法二

# 查询每个学生所在的班级

ret = Student.objects.all().select_related('classes').values('name', 'classes__name')

ret = Student.objects.all().select_related('classes').filter(age=18)

4..prefetch_related()

• 对于多对多字段（ManyToManyField）和一对多（反向查询），可以使用prefetch_related()来进行优化
• prefetch_related()的优化方式是分别查询每个表，然后用Python处理他们之间的关系
• 性能相关：多表连表操作时速度会慢，使用其执行多次SQL查询在Python代码中实现连表操作

• 原理

1. select * form classes;

2. 通过python代码获取id: c_id=[1, 2 ,3]

3. select * from teacher where id in c_id

• 错误用法

• 注意: .prefetch_related() 一定要传参，否则和普通的.all()方法的性能一样

teacher_list = Teacher.objects.all().prefetch_related()

for teacher in teacher_list:

    for classes in teacher.classes.all():

        print(classes.name)  # 一班

"""

    所执行的SQL语句次数

        SELECT "app01_teacher"."id", "app01_teacher"."name" FROM "app01_teacher"; args=()

        SELECT "app01_classes"."id", "app01_classes"."name" FROM "app01_classes" INNER JOIN "app01_teacher_classes" ON ("app01_classes"."id" = "app01_teacher_classes"."classes_id") WHERE "app01_teacher_classes"."teacher_id" = 1; args=(1,)

        SELECT "app01_classes"."id", "app01_classes"."name" FROM "app01_classes" INNER JOIN "app01_teacher_classes" ON ("app01_classes"."id" = "app01_teacher_classes"."classes_id") WHERE "app01_teacher_classes"."teacher_id" = 2; args=(2,)

        SELECT "app01_classes"."id", "app01_classes"."name" FROM "app01_classes" INNER JOIN "app01_teacher_classes" ON ("app01_classes"."id" = "app01_teacher_classes"."classes_id") WHERE "app01_teacher_classes"."teacher_id" = 3; args=(3,)

"""

• 多对多的正向查询

• 语法: .prefetch_related('类的ManyToManyField属性名', ……)

teacher_list = Teacher.objects.all().prefetch_related('classes')

for teacher in teacher_list:

    for classes in teacher.classes.all():

        print(classes.name)  # 一班

"""

    所执行的SQL语句次数

        select "app01_teacher"."id", "app01_teacher"."name" from "app01_teacher"; args=()

        select ("app01_teacher_classes"."teacher_id") as "_prefetch_related_val_teacher_id", "app01_classes"."id", "app01_classes"."name" from "app01_classes" inner join "app01_teacher_classes" on ("app01_classes"."id" = "app01_teacher_classes"."classes_id") where "app01_teacher_classes"."teacher_id" in (1, 2, 3); args=(1, 2, 3)

"""

• 多对多的反向查询

• 语法一: .prefetch_related('主表的类名_set', ……) 
• 语法二: .prefetch_related('related_name所设置的名字', ……)

classes_list = Classes.objects.all().prefetch_related('teacher_set')

for classes in classes_list:

    for teacher in classes.teacher_set.all():

        print(teacher.name)  # 李老师

"""

    所执行的SQL语句次数

        select "app01_classes"."id", "app01_classes"."name" from "app01_classes"; args=()

        select ("app01_teacher_classes"."classes_id") as "_prefetch_related_val_classes_id", "app01_teacher"."id", "app01_teacher"."name" from "app01_teacher" inner join "app01_teacher_classes" on ("app01_teacher"."id" = "app01_teacher_classes"."teacher_id") where "app01_teacher_classes"."classes_id" in (1, 2); args=(1, 2)

"""

• 一对多的反向查询

• 语法一: .prefetch_related('主表的类名_set', ……) 
• 语法二: .prefetch_related('related_name所设置的名字', ……)

classes_list = Classes.objects.all().prefetch_related('student_set')

for classes in classes_list:

    for student in classes.student_set.all():

        print(student.name)  # Kevin

"""

    所执行的SQL语句次数

        select "app01_classes"."id", "app01_classes"."name" from "app01_classes"; args=()

        select "app01_student"."id", "app01_student"."name", "app01_student"."age", "app01_student"."classes_id", "app01_student"."student_info_id" from "app01_student" where "app01_student"."classes_id" in (1, 2); args=(1, 2)

"""

• 其他用法

# 查询每个班级下的学生

ret = Classes.objects.prefetch_related().values('name', 'student__name')